Skip Headers

Oracle® Database PL/SQL User's Guide and Reference 10g Release 2 (10.2) Part Number B14261-01 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# SELECT INTO Statement

The `SELECT` `INTO` statement retrieves data from one or more database tables, and assigns the selected values to variables or collections. For a full description of the `SELECT` SQL statement, see Oracle Database SQL Reference.

In its default usage (`SELECT ... INTO`), this statement retrieves one or more columns from a single row. In its bulk usage (`SELECT ... BULK COLLECT INTO`), this statement retrieves an entire result set at once.

Syntax

select into statement ::=

Description of the illustration select_into_statement.gif

select item ::=

Description of the illustration select_item.gif

Keyword and Parameter Description

alias

Another (usually short) name for the referenced column, table, or view.

BULK COLLECT

Stores result values in one or more collections, for faster queries than loops with `FETCH` statements. For more information, see "Reducing Loop Overhead for DML Statements and Queries with Bulk SQL".

collection_name

A declared collection into which `select_item` values are fetched. For each `select_item`, there must be a corresponding, type-compatible collection in the list.

function_name

A user-defined function.

host_array_name

An array (declared in a PL/SQL host environment and passed to PL/SQL as a bind variable) into which `select_item` values are fetched. For each `select_item`, there must be a corresponding, type-compatible array in the list. Host arrays must be prefixed with a colon.

numeric_literal

A literal that represents a number or a value implicitly convertible to a number.

parameter_name

A formal parameter of a user-defined function.

record_name

A user-defined or `%ROWTYPE` record into which rows of values are fetched. For each `select_item` value returned by the query, there must be a corresponding, type-compatible field in the record.

rest_of_statement

Anything that can follow the `FROM` clause in a SQL `SELECT` statement (except the `SAMPLE` clause).

schema_name

The schema containing the table or view. If you omit `schema_name`, Oracle assumes the table or view is in your schema.

subquery

A `SELECT` statement that provides a set of rows for processing. Its syntax is similar to that of `select_into_statement` without the `INTO` clause.

table_reference

A table or view that must be accessible when you execute the `SELECT` statement, and for which you must have `SELECT` privileges. For the syntax of `table_reference`, see "DELETE Statement".

TABLE (subquery2)

The operand of `TABLE` is a `SELECT` statement that returns a single column value, which must be a nested table or a varray. Operator `TABLE` informs Oracle that the value is a collection, not a scalar value.

variable_name

A previously declared variable into which a `select_item` value is fetched. For each `select_item` value returned by the query, there must be a corresponding, type-compatible variable in the list.

Usage Notes

By default, a `SELECT` `INTO` statement must return only one row. Otherwise, PL/SQL raises the predefined exception `TOO_MANY_ROWS` and the values of the variables in the `INTO` clause are undefined. Make sure your `WHERE` clause is specific enough to only match one row

If no rows are returned, PL/SQL raises `NO_DATA_FOUND`. You can guard against this exception by selecting the result of an aggregate function, such as `COUNT(*)` or `AVG()`, where practical. These functions are guaranteed to return a single value, even if no rows match the condition.

A `SELECT ... BULK COLLECT INTO` statement can return multiple rows. You must set up collection variables to hold the results. You can declare associative arrays or nested tables that grow as needed to hold the entire result set.

The implicit cursor `SQL` and its attributes `%NOTFOUND`, `%FOUND`, `%ROWCOUNT`, and `%ISOPEN` provide information about the execution of a `SELECT` `INTO` statement.

Examples

Example 13-4 shows various ways to use the `SELECT` `INTO` statement.

Example 13-4 Using the SELECT INTO Statement

    DECLARE
      deptid        employees.department_id%TYPE;
      jobid         employees.job_id%TYPE;
      emp_rec       employees%ROWTYPE;
      TYPE emp_tab IS TABLE OF employees%ROWTYPE INDEX BY PLS_INTEGER;
      all_emps      emp_tab;
    BEGIN
      SELECT department_id, job_id INTO deptid, jobid
         FROM employees WHERE employee_id = 140;
      IF SQL%FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Dept Id: ' || deptid || ', Job Id: ' || jobid);
      END IF;
      SELECT * INTO emp_rec FROM employees WHERE employee_id = 105;
      SELECT * BULK COLLECT INTO all_emps FROM employees;
      DBMS_OUTPUT.PUT_LINE('Number of rows: ' || SQL%ROWCOUNT);
    END;
    /

For examples, see the following:

Example 1-3, "Assigning Values to Variables by SELECTing INTO"
Example 1-4, "Assigning Values to Variables as Parameters of a Subprogram"
Example 1-9, "Using WHILE-LOOP for Control"
Example 5-51, "Updating a Row Using a Record"
Example 5-52, "Using the RETURNING Clause with a Record"
Example 6-5, "Using CURRVAL and NEXTVAL"
Example 6-37, "Using ROLLBACK"
Example 6-38, "Using SAVEPOINT With ROLLBACK"
Example 6-43, "Declaring an Autonomous Function in a Package"
Example 7-1, "Examples of Dynamic SQL"
Example 7-2, "Dynamic SQL Procedure that Accepts Table Name and WHERE Clause"

Related Topics

"Assignment Statement"
"FETCH Statement"
"%ROWTYPE Attribute"